package org.dromara.common.mybatis.utils;

import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.dromara.common.mybatis.core.page.CustomConditions;

import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @ClassName CustomQueryBuilder
 * @Description 自定义查询构建器
 * @Author longlongxiaogege
 * @Date 2024/8/13 16:10
 * @Version 1.0
 */
public class CustomQueryBuilder {

    /**
     * 动态构建自定义SQL查询，排除某些字段
     *
     * @param customSql      自定义SQL查询字符串
     * @param conditions     查询条件
     * @param excludeColumns 排除的列名
     * @param columnMapping  conditions里字段到sql里字段名映射，实现改名(无需考虑驼峰和下划线)
     * @return 构建好的SQL查询字符串
     */
    public static String buildCustomSqlExcludeColumns(String customSql, CustomConditions conditions, List<String> excludeColumns, Map<String, String> columnMapping) {
        // 校验自定义SQL是否合法
        validateCustomSql(customSql);
        StringBuilder sqlBuilder = new StringBuilder(customSql);
        // 遍历所有条件，处理每个条件
        for (CustomConditions.Condition condition : conditions.getFilters()) {
            String originalColumn = condition.getColumn();
            // 检查是否在排除列表中
            if (!excludeColumns.contains(originalColumn)) {
                // 获取实际的列名，如果映射不存在则使用原始列名
                String actualColumn = columnMapping.getOrDefault(originalColumn, originalColumn);
                // 规范化列名（将驼峰命名转换为下划线命名）
                String normalizedColumn = normalizeColumnName(actualColumn);
                // 追加条件到SQL字符串
                appendCondition(sqlBuilder, normalizedColumn, condition);
            }
        }
        return sqlBuilder.toString();
    }

    /**
     * 动态构建自定义SQL查询，只包含某些字段
     *
     * @param customSql      自定义SQL查询字符串
     * @param conditions     查询条件
     * @param includeColumns 包含的列名
     * @param columnMapping  conditions里字段到sql里字段名映射，实现改名(无需考虑驼峰和下划线)
     * @return 构建好的SQL查询字符串
     */
    public static String buildCustomSqlIncludeColumns(String customSql, CustomConditions conditions, List<String> includeColumns, Map<String, String> columnMapping) {
        // 校验自定义SQL是否合法
        validateCustomSql(customSql);
        StringBuilder sqlBuilder = new StringBuilder(customSql);
        // 遍历所有条件，处理每个条件
        for (CustomConditions.Condition condition : conditions.getFilters()) {
            String originalColumn = condition.getColumn();
            // 检查是否在包含列表中
            if (includeColumns.contains(originalColumn)) {
                // 获取实际的列名，如果映射不存在则使用原始列名
                String actualColumn = columnMapping.getOrDefault(originalColumn, originalColumn);
                // 规范化列名（将驼峰命名转换为下划线命名）
                String normalizedColumn = normalizeColumnName(actualColumn);
                // 追加条件到SQL字符串
                appendCondition(sqlBuilder, normalizedColumn, condition);
            }
        }
        return sqlBuilder.toString();
    }

    /**
     * 过滤并追加条件到 QueryWrapper
     *
     * @param queryWrapper   QueryWrapper 对象
     * @param conditions     查询条件
     * @param excludeColumns 排除的列名
     * @param columnMapping  conditions里字段到sql里字段名映射，实现改名(无需考虑驼峰和下划线)
     */
    public static void filterConditionsForQueryWrapper(QueryWrapper<?> queryWrapper, CustomConditions conditions, List<String> excludeColumns, Map<String, String> columnMapping) {
        // 遍历所有条件，处理每个条件
        for (CustomConditions.Condition condition : conditions.getFilters()) {
            String originalColumn = condition.getColumn();
            // 检查是否在排除列表中
            if (!excludeColumns.contains(originalColumn)) {
                // 获取实际的列名，如果映射不存在则使用原始列名
                String actualColumn = columnMapping.getOrDefault(originalColumn, originalColumn);
                // 规范化列名（将驼峰命名转换为下划线命名）
                String normalizedColumn = normalizeColumnName(actualColumn);
                // 追加条件到QueryWrapper对象
                appendConditionToQueryWrapper(queryWrapper, normalizedColumn, condition);
            }
        }
    }

    /**
     * 校验自定义SQL的合法性
     *
     * @param customSql 自定义SQL字符串
     */
    private static void validateCustomSql(String customSql) {
        // 检查SQL字符串是否为空，且是否以"select"开头
        if (StrUtil.isEmpty(customSql) || !customSql.toLowerCase().startsWith("select")) {
            throw new IllegalArgumentException("Invalid SQL query. It must start with 'SELECT'.");
        }
    }

    /**
     * 追加条件到SQL字符串
     *
     * @param sb        SQL字符串构建器
     * @param column    列名
     * @param condition 条件对象
     */
    public static void appendCondition(StringBuilder sb, String column, CustomConditions.Condition condition) {
        String action = condition.getAction();
        String value = condition.getValue();
        // 特殊处理accept_user_id字段
        if ("accept_user_id".equals(column)) {
            sb.append(StrUtil.format(" AND ({} = '{}' OR {} = '' OR {} IS NULL)", column, value, column, column));
            return;
        }
        // 根据操作类型追加不同的条件到SQL字符串
        switch (action) {
            case "in":
                String inSql = Arrays.stream(value.split(",")).map(val -> "'" + val + "'").collect(Collectors.joining(","));
                sb.append(StrUtil.format(" AND `{}` IN ({}) ", column, inSql));
                break;
            case "notin":
                String notInSql = Arrays.stream(value.split(",")).map(val -> "'" + val + "'").collect(Collectors.joining(","));
                sb.append(StrUtil.format(" AND `{}` NOT IN ({}) ", column, notInSql));
                break;
            case "==":
                sb.append(StrUtil.format(" AND `{}` = '{}' ", column, value));
                break;
            case "!=":
                sb.append(StrUtil.format(" AND `{}` != '{}' ", column, value));
                break;
            case ">":
                sb.append(StrUtil.format(" AND `{}` > '{}' ", column, value));
                break;
            case "<":
                sb.append(StrUtil.format(" AND `{}` < '{}' ", column, value));
                break;
            case ">=":
                sb.append(StrUtil.format(" AND `{}` >= '{}' ", column, value));
                break;
            case "<=":
                sb.append(StrUtil.format(" AND `{}` <= '{}' ", column, value));
                break;
            case "like":
                sb.append(StrUtil.format(" AND `{}` LIKE '%{}%' ", column, value));
                break;
            case "isNull":
                sb.append(StrUtil.format(" AND `{}` IS NULL ", column));
                break;
            case "isNotNull":
                sb.append(StrUtil.format(" AND `{}` IS NOT NULL ", column));
                break;
            case "isStr":
                sb.append(StrUtil.format(" AND `{}` = '' ", column));
                break;
            case "isNotStr":
                sb.append(StrUtil.format(" AND `{}` != '' ", column));
                break;
            case "betweeen":
                String[] values = value.split(",");
                if (values.length == 2) {
                    if (isNumeric(values[0]) && isNumeric(values[1])) {
                        LocalDateTime startTime = Instant.ofEpochMilli(Long.parseLong(values[0])).atZone(ZoneOffset.ofHours(8)).toLocalDateTime();
                        LocalDateTime endTime = Instant.ofEpochMilli(Long.parseLong(values[1])).atZone(ZoneOffset.ofHours(8)).toLocalDateTime();
                        sb.append(StrUtil.format(" AND `{}` BETWEEN '{}' AND '{}' ", column, startTime, endTime));
                    } else {
                        sb.append(StrUtil.format(" AND `{}` BETWEEN '{}' AND '{}' ", column, values[0], values[1]));
                    }
                }
                break;
            default:
                break;
        }
    }

    /**
     * 追加条件到 QueryWrapper
     *
     * @param queryWrapper QueryWrapper 对象
     * @param column       列名
     * @param condition    条件对象
     */
    public static void appendConditionToQueryWrapper(QueryWrapper<?> queryWrapper, String column, CustomConditions.Condition condition) {
        String action = condition.getAction();
        String value = condition.getValue();
        // 根据操作类型追加不同的条件到QueryWrapper对象
        switch (action) {
            case "in":
                List<String> inValues = Arrays.stream(value.split(",")).map(String::trim).collect(Collectors.toList());
                queryWrapper.in(column, inValues);
                break;
            case "notin":
                List<String> notInValues = Arrays.stream(value.split(",")).map(String::trim).collect(Collectors.toList());
                queryWrapper.notIn(column, notInValues);
                break;
            case "==":
                queryWrapper.eq(column, value);
                break;
            case "!=":
                queryWrapper.ne(column, value);
                break;
            case ">":
                queryWrapper.gt(column, value);
                break;
            case "<":
                queryWrapper.lt(column, value);
                break;
            case ">=":
                queryWrapper.ge(column, value);
                break;
            case "<=":
                queryWrapper.le(column, value);
                break;
            case "like":
                queryWrapper.like(column, value);
                break;
            case "isNull":
                queryWrapper.isNull(column);
                break;
            case "isNotNull":
                queryWrapper.isNotNull(column);
                break;
            case "isStr":
                queryWrapper.eq(column, "");
                break;
            case "isNotStr":
                queryWrapper.ne(column, "");
                break;
            case "betweeen":
                String[] values = value.split(",");
                if (values.length == 2) {
                    if (isNumeric(values[0]) && isNumeric(values[1])) {
                        LocalDateTime startTime = Instant.ofEpochMilli(Long.parseLong(values[0])).atZone(ZoneOffset.ofHours(8)).toLocalDateTime();
                        LocalDateTime endTime = Instant.ofEpochMilli(Long.parseLong(values[1])).atZone(ZoneOffset.ofHours(8)).toLocalDateTime();
                        queryWrapper.between(column, startTime, endTime);
                    } else {
                        queryWrapper.between(column, values[0], values[1]);
                    }
                }
                break;
            default:
                break;
        }
    }

    /**
     * 校验是否为数字
     *
     * @param str 字符串
     * @return 是否为数字
     */
    private static boolean isNumeric(String str) {
        return str != null && str.matches("\\d+");
    }

    /**
     * 将驼峰命名法转换为下划线命名法
     *
     * @param str 驼峰命名法字符串
     * @return 下划线命名法字符串
     */
    public static String camelToSnake(String str) {
        StringBuilder result = new StringBuilder();
        for (int i = 0; str != null && i < str.length(); i++) {
            char c = str.charAt(i);
            if (Character.isUpperCase(c)) {
                if (i > 0) {
                    result.append('_');
                }
                result.append(Character.toLowerCase(c));
            } else {
                result.append(c);
            }
        }
        return result.toString();
    }

    /**
     * 规范化列名，将驼峰命名法转换为下划线命名法
     *
     * @param column 列名
     * @return 规范化后的列名
     */
    public static String normalizeColumnName(String column) {
        return camelToSnake(column);
    }
}
